import pymysql


# 创建类封装方法
# 1.封装连接数据库方法
class DBUtil:
  conn = None

  @classmethod
  def __connection_db(cls):
    cls.conn = pymysql.connect(host="211.103.136.244", port=7061, user="student", password="iHRM_student_2022",
                         database="ihrm", charset="utf8")
    return cls.conn

  # 2.封装关闭连接方法
  @classmethod
  def __close_db(cls):
    cls.conn.close()


# 3.封装查询数据的方法
  @classmethod
  def query_db(cls,sql):
    cursor = None
    result = None
    # 1.创建数据库连接
    try:
      cls.conn = cls.__connection_db()
      # 2.获取游标
      cursor = cls.conn.cursor()
      # 3.执行sql，返回查询结果
      cursor.execute(sql)
      result = cursor.fetchone()
    except Exception as err:
      print("查询数据库失败：", str(err))
    finally:
      # 4关闭游标
      cursor.close()
      # 5.关闭连接
      cls.__close_db()
      return result


  # 4.封装增删改数据的方法
  @classmethod
  def uid_db(cls, sql):
    cursor = None
    try:
      # 1.创建数据库连接
      cls.conn = cls.__connection_db()
      # 2.获取游标
      cursor = cls.conn.cursor()
      # 3.执行sql，打印影响的行数
      cursor.execute(sql)
      affect_rows = cls.conn.affected_rows()
      print("影响的行数是：", affect_rows)
      # 4.事务的提交，回滚
      cls.conn.commit()
    except Exception as err:
      print("增删改数据库时出错：", str(err))
      cls.conn.rollback()
    finally:
      # 5.关闭游标
      cursor.close()
      # 6.关闭连接
      cls.__close_db()
if __name__ == '__main__':
  query_sql="select * from t_book;"
  res=DBUtil.query_db(query_sql)
  print(res)

